from datetime import datetime
import time
from peewee import *
from peewee import Model
from openpyxl import load_workbook


def timestamp2timestr(timestamp):
    if timestamp > 9999999999:
        timestamp = timestamp // 1000
    time_str = time.localtime(timestamp)
    time_str = time.strftime("%Y-%m-%d %H:%M:%S", time_str)
    return time_str


# db = MySQLDatabase('weather_analysis', host='raspberrypi', port=3380, user='root', password='123456')
db = SqliteDatabase('weather_analysis.db')

class BaseModel(Model):
    _add_time = DateTimeField(default=datetime.now(), verbose_name='添加时间')

    class Meta:
        database = db


class CityInfo(BaseModel):
    adname = CharField(max_length=100, verbose_name="地区中文名称")
    adcode = IntegerField(default=0, verbose_name="地区代码")
    citycode = IntegerField(default=0, verbose_name="城市或所属城市代码")

    class Meta:
        table_name = "city_info"


class CityInfoTest(BaseModel):
    adname = CharField(max_length=100, verbose_name="地区中文名称")
    adcode = IntegerField(default=0, verbose_name="地区代码")
    citycode = IntegerField(default=0, verbose_name="城市或所属城市代码")

    class Meta:
        table_name = "city_info_copy"

def insertcityinfo():
    workbook = load_workbook(filename='AMap_adcode_citycode.xlsx')
    sheet = workbook.active
    ads = []
    for row in sheet.iter_rows(min_row=2, values_only=True):
        # 打印每行的数据
        ad = {}
        ad['adname'] = row[0]
        ad['adcode'] = row[1]
        ad['citycode'] = row[2]
        print(ad)
        ads.append(ad)
    CityInfo.insert_many(ads).execute()


class WeatherInfo(BaseModel):
    adcode = IntegerField(default=0, verbose_name="地区代码")
    province = CharField(max_length=50, verbose_name="城市名称")
    city = CharField(max_length=50, verbose_name="地区名称")
    reporttime = DateTimeField(verbose_name="天气上报时间")
    date = DateField(verbose_name="天气日期")
    dayweather = CharField(max_length=50, verbose_name="白天天气")
    nightweather = CharField(max_length=50, verbose_name="夜间天气")
    daytemp = IntegerField(default=0, verbose_name="白天温度")
    nighttemp = IntegerField(default=0, verbose_name="夜间温度")
    daywind = CharField(max_length=50, verbose_name="白天风向")
    nightwind = CharField(max_length=50, verbose_name="夜间风向")
    daypower = CharField(max_length=50, verbose_name="白天风级")
    nightpower = CharField(max_length=50, verbose_name="夜间风级")
    class Meta:
        table_name = "weather_info"

def init_table(table):
    db.create_tables(table)



if __name__ == '__main__':
    # print(timestamp2timestr(time.time()))
    # init_table([WeatherInfo,CityInfo])
    insertcityinfo()

